package com.crashcollection.util.http;

import java.util.ArrayList;
import java.util.List;

import com.crashcollection.lib.CrashInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.text.TextUtils;

public class CrashInfoSQLiteHelper extends SQLiteOpenHelper {
	                                                                                //
	private final static int DATABASE_VERSION = 3;                                  // 数据库版本

	private final static String DATABASE_NAME = "crash_info";                       // 数据库名
	private final static String TABLE_NAME = "crash_items";                         // 表名
	
	private final static String COLUMN_CRASH_TIME = "crash_time";                   // 异常时间
	private final static String COLUMN_ANDROID_SDK = "android_sdk";                 // android sdk
	private final static String COLUMN_ANDROID_RELEASE = "android_release";         // android release
	private final static String COLUMN_LANGUAGE = "language";                       // 语言
	private final static String COLUMN_APP_VERSION = "app_version";                 // 版本
	private final static String COLUMN_NETWORK_TYPE = "network_type";               // 网络类型
	private final static String COLUMN_CRASH_TYPE = "crash_type";                   // 异常类型
	private final static String COLUMN_CRASH_INFO = "crash_info";                   // 异常内容
	private final static String COLUMN_CRASH_STACK_MD5 = "crash_stack_md5";         // stack md5
	private final static String COLUMN_UPLOADED = "uploaded";                       // 是否 上传

	public CrashInfoSQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE ").append(TABLE_NAME).append("(")
				.append(COLUMN_CRASH_TIME).append(" INTEGER PRIMARY KEY, ")
				.append(COLUMN_ANDROID_SDK).append(" INTEGER NOT NULL, ")
				.append(COLUMN_ANDROID_RELEASE).append(" TEXT NOT NULL, ")
				.append(COLUMN_LANGUAGE).append(" TEXT NOT NULL, ")
				.append(COLUMN_APP_VERSION).append(" INTEGER NOT NULL, ")
				.append(COLUMN_NETWORK_TYPE).append(" TEXT NOT NULL, ")
				.append(COLUMN_CRASH_TYPE).append(" TEXT NOT NULL, ")
				.append(COLUMN_CRASH_INFO).append(" TEXT NOT NULL, ")
				.append(COLUMN_CRASH_STACK_MD5)
				.append(" TEXT NOT NULL DEFAULT '', ").append(COLUMN_UPLOADED)
				.append(" INTEGER NOT NULL DEFAULT 0);");

		db.execSQL(sql.toString());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		StringBuilder greaterThan2 = new StringBuilder();
		greaterThan2.append("ALTER TABLE ").append(TABLE_NAME)
				.append(" ADD COLUMN ").append(COLUMN_CRASH_STACK_MD5)
				.append(" TEXT NOT NULL DEFAULT ''");

		StringBuilder greaterThan3 = new StringBuilder();
		greaterThan3.append("ALTER TABLE ").append(TABLE_NAME)
				.append(" ADD COLUMN ").append(COLUMN_UPLOADED)
				.append(" INTEGER NOT NULL DEFAULT 0");

		switch (oldVersion) {
		case 1:
			if (newVersion >= 2) {
				db.execSQL(greaterThan2.toString());
			}
			if (newVersion >= 3) {
				db.execSQL(greaterThan3.toString());
			}
			break;
		case 2:
			if (newVersion >= 3) {
				db.execSQL(greaterThan3.toString());
			}
			break;
		default:
			break;
		}
	}

	public void insertCrashInfo(CrashInfo crashInfo) {
		SQLiteDatabase db = getWritableDatabase();
		String md5 = crashInfo.getCrashStackMd5();
		if (!isMd5Exist(db, md5)) {
			ContentValues values = new ContentValues();
			values.put(COLUMN_CRASH_TIME, crashInfo.getCrashTime());
			values.put(COLUMN_ANDROID_SDK, crashInfo.getAndroidSdk());
			values.put(COLUMN_ANDROID_RELEASE, crashInfo.getAndroidRelease());
			values.put(COLUMN_LANGUAGE, crashInfo.getLanguage());
			values.put(COLUMN_APP_VERSION, crashInfo.getAppVersion());
			values.put(COLUMN_NETWORK_TYPE, crashInfo.getNetworkType());
			values.put(COLUMN_CRASH_TYPE, crashInfo.getCrashType());
			values.put(COLUMN_CRASH_INFO, crashInfo.getCrashInfo());
			values.put(COLUMN_CRASH_STACK_MD5, md5);

			db.insert(TABLE_NAME, null, values);
		}
		db.close();
	}

	public List<CrashInfo> getCrashInfo(int limit) {
		List<CrashInfo> list = new ArrayList<CrashInfo>();
		SQLiteDatabase db = getReadableDatabase();

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM ").append(TABLE_NAME).append(" WHERE ")
				.append(COLUMN_UPLOADED).append(" = 0 LIMIT ").append(limit);
		Cursor cursor = db.rawQuery(sql.toString(), null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			list.add(cursorToItem(cursor));
			cursor.moveToNext();
		}
		cursor.close();
		db.close();

		return list;
	}

	private CrashInfo cursorToItem(Cursor cursor) {
		CrashInfo item = new CrashInfo();

		int columnIndex = cursor.getColumnIndex(COLUMN_CRASH_TIME);
		item.setCrashTime(cursor.getLong(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_ANDROID_SDK);
		item.setAndroidSdk(cursor.getInt(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_ANDROID_RELEASE);
		item.setAndroidRelease(cursor.getString(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_LANGUAGE);
		item.setLanguage(cursor.getString(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_APP_VERSION);
		item.setAppVersion(cursor.getInt(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_NETWORK_TYPE);
		item.setNetworkType(cursor.getString(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_CRASH_TYPE);
		item.setCrashType(cursor.getString(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_CRASH_INFO);
		item.setCrashInfo(cursor.getString(columnIndex));
		columnIndex = cursor.getColumnIndex(COLUMN_CRASH_STACK_MD5);
		item.setCrashStackMd5(cursor.getString(columnIndex));

		return item;
	}

	public void setUploaded(List<Long> crashTimeList) {
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();
		try {
			for (long crashTime : crashTimeList) {
				ContentValues values = new ContentValues();
				values.put(COLUMN_UPLOADED, 1);
				db.update(TABLE_NAME, values, COLUMN_CRASH_TIME + " = "
						+ crashTime, null);
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
			db.close();
		}
	}

	private boolean isMd5Exist(SQLiteDatabase db, String md5) {
		if (TextUtils.isEmpty(md5)) {
			return false;
		}

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM ").append(TABLE_NAME)
				.append(" WHERE ").append(COLUMN_CRASH_STACK_MD5)
				.append(" = '").append(md5).append("'");

		SQLiteStatement s = db.compileStatement(sql.toString());
		long ret = s.simpleQueryForLong();
		s.close();
		return (ret == 0) ? false : true;
	}

	public void deleteUnusedItems() {
		SQLiteDatabase db = getWritableDatabase();
		long maxVersion = getMaxVersion(db);
		db.delete(TABLE_NAME, COLUMN_UPLOADED + " = ? AND "
				+ COLUMN_APP_VERSION + " < ?",
				new String[] { "1", String.valueOf(maxVersion) });
		db.close();
	}

	private long getMaxVersion(SQLiteDatabase db) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT MAX(").append(COLUMN_APP_VERSION).append(") FROM ")
				.append(TABLE_NAME);

		SQLiteStatement s = db.compileStatement(sql.toString());
		long ret = s.simpleQueryForLong();
		s.close();
		return ret;
	}

}
